package com.iknight.mgr.core.utils;

import com.iknight.mgr.core.constant.IkgExcelParseCode;
import net.sf.json.JSONObject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by jeryzym on 2017/9/21.
 * Excel工具类
 */
public final class ExcelUtils {

    private final static Logger logger = LogManager.getLogger(ExcelUtils.class);

    /**
     * @param code     websocket code
     * @param fis      读取文件流
     * @param template 文件解析参考模板
     *                 template pattern
     *                 {
     *                 sheet:{
     *                 index:1,//用于定义excel表中的sheet页序号
     *                 entity:{
     *                 entityName:实例名称
     *                 tableName:表的名字，用于写入数据使用,
     *                 columns:{//列属性定义
     *                 column:{fieldType:'string'},
     *                 column:{fieldType:'int',precision:2},
     *                 column:{fieldType:'date',pattern:'yyyy-MM-dd'}
     *                 .....
     *                 }
     *                 }
     *                 },...
     *                 }
     * @return pattern
     * {
     * parseStartTime:,
     * pasrseEndTime:,
     * parseMsg:'',
     * success:'',
     * errorCode:''
     * result:{
     * <p>
     * }
     * }
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static JSONObject parseExcelFile(String code, FileInputStream fis, JSONObject template) {
        JSONObject resultObject = new JSONObject();//定义返回结果

        resultObject.put(IkgExcelParseCode.PARSESTARTTIME, IkgStringUtils.getCurrentDate("yyyy-MM-dd HH:mm"));//读取解析开始时间

        StringBuffer msg = new StringBuffer();
        String message = null;//用于前台输出

        //文件流读取
        OPCPackage pkg = null;
        try {
            pkg = OPCPackage.open(fis);
            XSSFWorkbook workbook = new XSSFWorkbook(pkg);
            //获取需要解析的sheet页索引号，sheet页索引从0开始计数。第一页为0
            List<JSONObject> sheets = (List<JSONObject>) template.get("sheet");

            List<JSONObject> result = new ArrayList<>();

            for (int i = 0; i < sheets.size(); i++) {//根据定义的模板信息逐页遍历解析

                JSONObject entityJo = new JSONObject();//定义单页实体信息

                //获得对应的sheet页序号
                int index = IkgStringUtils.ObjectToInt(sheets.get(i).get("index"));
                message = "读取第" + index + "页数据";
                IkgWebUtils.sendMsg(code, message);

                //获得对应的sheet页信息
                XSSFSheet sheet = workbook.getSheetAt(index); //获取sheet

                int activeRowLength = sheet.getPhysicalNumberOfRows();

                //获得首行，用于校验数据列数是否与模板匹配
                XSSFRow row = sheet.getRow(0);
                //获得总列数
                int columnLength = row.getLastCellNum();
                //获取实体映射数据
                Map entity = (Map) sheets.get(i).get("entity");

                entityJo.put(IkgExcelParseCode.TABLENAME, entity.get("tableName"));
                entityJo.put(IkgExcelParseCode.ENTITYNAME, entity.get("entityName"));

                List<Map> columns = (List<Map>) entity.get("columns");

                if (columnLength != columns.size()) {//模板定义列数与读取文件的列数不一致
                    //列数和预定义读取模板列数不一致
                    msg.append("读取sheet页:[")
                            .append(sheet.getSheetName())
                            .append("]时，检测到有效列数与模板定义列数不一致，其中:有效列数为")
                            .append(columnLength)
                            .append(",模板列数为")
                            .append(columns.size())
                            .append(",读取异常;");
                    logger.info(msg.toString());

                    IkgWebUtils.sendMsg(code, msg.toString());
                }

                //用于封装数据
                List<JSONObject> rows = new ArrayList<>();
                for (int rowIndex = 1; rowIndex < activeRowLength; rowIndex++) {

                    row = sheet.getRow(rowIndex);

                    if (null != row && !isRowEmpty(row)) {

                        message = "分析sheet页[" + sheet.getSheetName() + "]，第" + rowIndex + "行数据";
                        IkgWebUtils.sendMsg(code, message);

                        JSONObject rowResult = getRowCellValue(code, row, entity);

                        logger.info(rowResult.toString());

                        if (rowResult.get("success").toString().equals("false")) {//读取时发生异常，读取中断,反馈具体问题

                            resultObject.put(IkgExcelParseCode.PASRSEENDTIME, IkgStringUtils.getCurrentDate("yyyy-MM-dd HH:mm"));
                            message = "读取sheet页[" + sheet.getSheetName() + "]，第" + rowIndex + "行" + rowResult.get("errorMsg");
                            IkgWebUtils.sendMsg(code, message);
                            resultObject.put(IkgExcelParseCode.ERRORMSG, message);
                            return resultObject;
                        } else {
                            JSONObject rowData = new JSONObject();
                            rowData.put("row", rowResult.get("row"));
                            rows.add(rowData);
                        }
                    } else {
                        logger.info("检测到空行:" + rowIndex);
                        message = "检测到第" + rowIndex + "行为空行，可忽略";
                        IkgWebUtils.sendMsg(code, message);
                    }
                }
                entityJo.put("data", rows);
                result.add(entityJo);
            }
            resultObject.put("result", result);
            pkg.close();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        resultObject.put(IkgExcelParseCode.PASRSEENDTIME, IkgStringUtils.getCurrentDate("yyyy-MM-dd HH:mm"));
        return resultObject;
    }

    /**
     * 根据定义的entity的列进行数据读取
     *
     * @param row
     * @param entity
     * @return
     */
    private static JSONObject getRowCellValue(String code, XSSFRow row, Map entity) {
        JSONObject result = new JSONObject();

        boolean success = true;

        StringBuffer errorMsg = new StringBuffer(100);

        String message = null;

        if (null == row || null == entity) {
            result.put(IkgExcelParseCode.ERRORMSG, "传入参数为空");
            result.put(IkgExcelParseCode.ERRORCODE, IkgExcelParseCode.NULLPARAM);
            result.put(IkgExcelParseCode.SUCCESS, false);
            return result;
        }

        List<Map> columns = (List<Map>) entity.get("columns");
        int definedColumnNum = columns.size();

        List<JSONObject> rows = new ArrayList<>();
        for (int i = 0; i < definedColumnNum; i++) {//解析单元格数据
            //获得第i个单元格，并进行格式匹配
            XSSFCell cell = row.getCell(i);

            Map column = columns.get(i);
            String fieldName = IkgStringUtils.ObjectToString(column.get("fieldName"));//列名
            String fieldType = IkgStringUtils.ObjectToString(column.get("fieldType"));//字段类型，根据字段类型不同进行格式转换
            Object fieldValue = null;//单元格值

            JSONObject rowValue = new JSONObject();
            rowValue.put("datatype", fieldType);

            switch (fieldType) {//根据数据类型分析
                case "string": {
                    int minLength = IkgStringUtils.ObjectToInt(column.get("min"));
                    int maxLength = IkgStringUtils.ObjectToInt(column.get("max"));
                    if (null == cell) {
                        fieldValue = "";
                        logger.warn("检测到空单元格，预定义string类型，默认为空");

                        message = "检测到空单元格，预定义string类型，默认为空";
                        IkgWebUtils.sendMsg(code, message);
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        fieldValue = cell.getStringCellValue();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        fieldValue = String.valueOf(cell.getNumericCellValue());
                    }

                    if (maxLength != 0 && IkgStringUtils.ObjectToString(fieldValue).length() > maxLength) {
                        success = false;
                        errorMsg.append("第").append(i + 1).append("单元格，数据超出限定长度，读取终止");
                    }
                    if (minLength != 0 && IkgStringUtils.ObjectToString(fieldValue).length() < minLength) {
                        success = false;
                        errorMsg.append("第").append(i + 1).append("单元格，数据低于限定长度，读取终止");
                    }
                    rowValue.put(fieldName, fieldValue);
                    rowValue.put("max", maxLength);
                    rowValue.put("min", minLength);
                    break;
                }
                case "number": {
                    if (null == cell) {
                        logger.warn("检测到空单元格,预定义number类型，默认补录数据为0");
                        message = "检测到空单元格，预定义number类型，默认补录数据为0";
                        IkgWebUtils.sendMsg(code, message);

                        fieldValue = 0;
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        fieldValue = cell.getNumericCellValue();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        fieldValue = Double.valueOf(cell.getStringCellValue());
                    }
                    rowValue.put(fieldName, fieldValue);
                    rowValue.put("precision", IkgStringUtils.ObjectToInt(column.get("precision")));//精度
                    break;
                }
                case "date": {
                    rowValue.put(fieldName, cell.getStringCellValue());
                    rowValue.put("pattern", IkgStringUtils.ObjectToString(column.get("pattern")));//获取日期格式
                    break;
                }
            }
//            }
            rows.add(rowValue);
        }
        result.put("row", rows);
        result.put(IkgExcelParseCode.SUCCESS, success);
        if (!success) {
            result.put(IkgExcelParseCode.ERRORMSG, errorMsg);
            IkgWebUtils.sendMsg(code, errorMsg.toString());
        }
        return result;
    }

    private static boolean isRowEmpty(Row row) {
        if (null == row) {
            return false;
        } else {
            for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    return false;
                }
            }
            return true;
        }
    }
}
